#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive


${HIVE_HOME} -S -e "
insert overwrite table ol_edu_dwb.dwb_intent_detail
select
        fir.intent_id,
        fir.create_date_time,
        fir.update_date_time,
        fir.intent_deleted,
        fir.customer_id,
        fir.origin_type,
        substring(create_date_time,1,4) as year,
        substring(create_date_time,1,7) as month,
        substring(create_date_time,1,10) as day,
        dic.area,
        fic.clue_id,
        fic.customer_relationship_id,
        fic.clue_state,
        fic.itcast_school_id,
        fic.itcast_school,
        fic.itcast_subject_id,
        fic.itcast_subject,
        fic.origin_channel,
        die.employee_id,
        did.department_id,
        did.department_name,
        did.parent_id
--from ol_edu_dwd.fact_intent_clue fic
--left join ol_edu_dwd.fact_intent_relationship fir on fic.customer_relationship_id=fir.intent_id
--left join ol_edu_dwd.dim_intent_customer dic on fir.customer_id = dic.customer_id
--left join ol_edu_dwd.dim_intent_employee die on fir.creator=die.employee_id
--left join ol_edu_dwd.dim_intent_department did on die.tdepart_id=did.department_id;
from  ol_edu_dwd.fact_intent_relationship fir
left join ol_edu_dwd.fact_intent_clue fic on fic.customer_relationship_id=fir.intent_id
left join ol_edu_dwd.dim_intent_customer dic on fir.customer_id = dic.customer_id
left join ol_edu_dwd.dim_intent_employee die on fir.creator=die.employee_id
left join ol_edu_dwd.dim_intent_department did on die.tdepart_id=did.department_id;

insert overwrite table ol_edu_dwb.dwb_clue_detail
select
        fir.create_date_time,
        fir.origin_type,
        fic.clue_state,
        fic.clue_id,
        dia.appeal_status,
        substring(create_date_time,1,10) as day,
        substring(create_date_time,1,13) as hour
from ol_edu_dwd.fact_intent_clue fic
left join ol_edu_dwd.fact_intent_relationship fir on fic.customer_relationship_id=fir.intent_id
left join ol_edu_dwd.dim_intent_appeal dia on fir.intent_id=dia.customer_relationship_first_id;
"